﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using DTO;
using DAO;


namespace DAO

{
    public class TongKetMonDAO
    {
         //Danh Sach ket qua hoc sinh theo mon hoc va hoc ky
        public static DataTable DanhSachTheoMonHocVaHocKy(string maMonHoc, string hocKy)
        { 

            string command = 
                "select KetQuaMonHoc.*, HocSinh.maLop" 
                +" from KetQuaMonHoc, HocSinh"
                +" where KetQuaMonHoc.maHocSinh = Hocsinh.maHocSinh"
                + " and"
                + " KetQuaMonHoc.maMonHoc ='" + maMonHoc +"'"
                + " and KetQuaMonHoc.hocKy = '" + hocKy + "'"
                ;
            DataTable tb  = new DataTable();
            try{
                tb = DaTaProvider.excecuteQuery( command );
            }
            catch( Exception ex)
            {
                throw ex;
            }
            return tb;
        }
        #region lay ma mon hoc
        public static DataTable LayMaMonHoc(string tenMonHoc)
        { 

                string command = "select MonHoc.maMonHoc from MonHoc"
                                + " where MonHoc.tenMonHoc = N'" + tenMonHoc + "'";

                DataTable tb = new DataTable();
                try
                {
                    tb = DaTaProvider.excecuteQuery(command);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return tb;

        }
        #endregion 

       public static int LoadDuLieuXuong(DataTable tb)
        {
            string command;
            string maMonHoc; string hocKy; string maLop; int siSo; int soLuongDat; float tyLe;

            DataTable tb2 = tb;
            int irow = tb.Rows.Count;
            int icolumn = tb.Columns.Count;

            int flag = 1;
            for (int i = 0; i < irow; i++)
            {
               
                    maMonHoc = tb.Rows[i][0].ToString();
                    hocKy = tb.Rows[i][1].ToString();
                    maLop = tb.Rows[i][2].ToString();
                    siSo = int.Parse( tb.Rows[i][3].ToString() );
                    soLuongDat = int.Parse( tb.Rows[i][4].ToString() );
                    tyLe = float.Parse( tb.Rows[i][5].ToString() );

                    command = "insert into TongKetMonHoc( maMonHoc, hocKy, maLop, siSo, soLuongDat, tyLe )"
                                + " values( '" + maMonHoc + "','" + hocKy + "', '" + maLop + "', " + siSo.ToString() + ", " + soLuongDat.ToString() + ", " + tyLe.ToString() + " )";

                    flag =  DaTaProvider.ExcecuteNonQuery( command );
                    if (flag == 0)
                        return 0;
            }
            return 1;

        }

        

        #region tinh diem trung binh
        public static float DiemTrungBinh(string diemMuoiLamPhut, string diemMotTiet, string diemGiuaKy, string diemCuoiKy)
        {
            float tongDiem = float.Parse(diemMuoiLamPhut) + float.Parse(diemMotTiet) + float.Parse(diemGiuaKy) + float.Parse(diemCuoiKy);
            return tongDiem / 4;
        }
        #endregion

        public static bool Insert( string maMonHoc,string  hocKy,string maLop,int siSo,int  soLuongDat,float tyLe)
        {
            try
            {

                string command = "insert into TongKetMonHoc( maMonHoc, hocKy, maLop, siSo, soLuongDat, tyLe )"
                                + " values( '" + maMonHoc + "','" + hocKy + "', '" + maLop + "', " + siSo.ToString() + ", " + soLuongDat.ToString() + ", " + tyLe.ToString() + " )";
                DaTaProvider.ExcecuteNonQuery(command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
       
        public static DataTable DanhSach(string tenMonHoc, string hocKy)
        {
            DataTable tbKetQua = new DataTable();
            // lay ma mon hoc
            DataTable tbMaMonHoc = TongKetMonDAO.LayMaMonHoc(tenMonHoc);
            string maMonHoc = tbMaMonHoc.Rows[0][0].ToString();

            // lay danh sach bang liet ke tu ten mon hoc va hoc ky
            DataTable tbDanhSachLietKe = TongKetMonDAO.DanhSachTheoMonHocVaHocKy(maMonHoc, hocKy);
            
            TongKetMon_LopDTO phanTuDuyet = new TongKetMon_LopDTO();

            // lay kieu dong tu bang KetQuaMonHoc
            DataTable tbTK = new DataTable();
            try
            {
                string command = "select * from TongKetMonHoc where TongKetMonHoc.maMonHoc = '" + maMonHoc + "' and TongKetMonHoc.hocKy = '" + hocKy + "'";
                tbTK = DaTaProvider.excecuteQuery(command);
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
            int ir = tbTK.Rows.Count;
            int icolumn = tbTK.Columns.Count;
            // xu ly du lieu co duoc
            int irow = tbDanhSachLietKe.Rows.Count;
            tbKetQua = tbTK.Clone();

            #region Thay đổi tên cột
            tbKetQua.Columns[0].ColumnName = "Mã Môn Học";
            tbKetQua.Columns[1].ColumnName = "Học Kỳ";
            tbKetQua.Columns[2].ColumnName = "Mã Lớp";
            tbKetQua.Columns[3].ColumnName = "Sĩ Số";
            tbKetQua.Columns[4].ColumnName = "số lượng đạt";
            tbKetQua.Columns[5].ColumnName = "Tỷ lệ đạt( % )";

            #endregion

            #region tạo mảng cờ : flag
            int[] flag = new int[irow];
            for( int i = 0; i < irow; i++ )
                flag[i] = 1;
            #endregion

            for (int i = 0; i < irow; i++)
            {
                if (flag[i] == 1)
                {
                    DataRow r = tbKetQua.NewRow();
                    flag[i] = 0;
                    phanTuDuyet.Lop = tbDanhSachLietKe.Rows[i][7].ToString();
                    phanTuDuyet.SiSo++;
                    if (TongKetMonDAO.DiemTrungBinh(
                                                    tbDanhSachLietKe.Rows[i][2].ToString(),
                                                    tbDanhSachLietKe.Rows[i][3].ToString(),
                                                    tbDanhSachLietKe.Rows[i][4].ToString(),
                                                    tbDanhSachLietKe.Rows[i][5].ToString()
                                                    ) >= QuyDinhDAO.DiemTrungBinh
                        )
                    {
                        phanTuDuyet.SoLuongDat++;
                    }

                    for (int j = i + 1; j < irow; j++)
                    {
                        if (flag[j] != 0 && string.Compare(phanTuDuyet.Lop, tbDanhSachLietKe.Rows[j][7].ToString()) == 0)
                        {
                            phanTuDuyet.SiSo++;

                            if (TongKetMonDAO.DiemTrungBinh(
                                                    tbDanhSachLietKe.Rows[j][2].ToString(),
                                                    tbDanhSachLietKe.Rows[j][3].ToString(),
                                                    tbDanhSachLietKe.Rows[j][4].ToString(),
                                                    tbDanhSachLietKe.Rows[j][5].ToString()
                                                    ) >= QuyDinhDAO.DiemTrungBinh
                        )
                            {
                                phanTuDuyet.SoLuongDat++;
                            }

                            flag[j] = 0;
                        }
                    }
                    phanTuDuyet.TyLe = phanTuDuyet.SoLuongDat * 1.0f / phanTuDuyet.SiSo * 100;

                    // dua phantuDuyet vao 1 dong du lieu de dua vao table
                    r[0] = maMonHoc;
                    r[1] = hocKy;
                    r[2] = phanTuDuyet.Lop;
                    r[3] = phanTuDuyet.SiSo;
                    r[4] = phanTuDuyet.SoLuongDat;
                    r[5] = phanTuDuyet.TyLe;
 
                    tbKetQua.Rows.Add( r );

                   
                    // điền dữ liệu xuống sql server
                   // Insert(maMonHoc, hocKy, phanTuDuyet.Lop, phanTuDuyet.SiSo, phanTuDuyet.SoLuongDat, phanTuDuyet.TyLe);
                }

               
            }


            return tbKetQua;

        }
        
        
    }
}
